from dataclasses import dataclass
from datetime import datetime
from pytz import timezone

from .my_cursor import MyCursor

TZ = timezone('Asia/Shanghai')
DATETIME_STR_FORMAT = "%Y-%m-%d %H:%M:%S"


@dataclass
class RankRecord():
    username : str
    step : int


class RankDatabase():
    # TODO: Should make me cache something to speed up.

    TABLE_NAME = 'rank_table'

    def IsUsernameInRankList(self, problem_id : int, username : str) -> bool:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select count(*) from {self.TABLE_NAME}
                where problem_id = {problem_id} and username = "{username}";
                """
            )
            count = int(cursor.fetchone()[0])

        return count != 0


    def IsCookieInRankList(self, cookie : str) -> bool:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select count(*) from {self.TABLE_NAME}
                where cookie = "{cookie}";
                """
            )
            count = int(cursor.fetchone()[0])

        return count != 0


    def SearchCookieInRankList(self, cookie_prefix : str) -> list[str]:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select distinct cookie from {self.TABLE_NAME}
                where cookie like "{cookie_prefix}%";
                """
            )
            rows = cursor.fetchall()

        return [str(row[0]) for row in rows]


    def GetUserCookie(self, problem_id : int, username : str) -> str:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select cookie from {self.TABLE_NAME} 
                where problem_id = {problem_id} and username = "{username}";
                """
            )

        return str(cursor.fetchone()[0])


    def GetRankList(self, problem_id : int) -> list[RankRecord]:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select username, step from {self.TABLE_NAME} 
                where problem_id = {problem_id}
                order by step, record_time;
                """
            )
            rows = cursor.fetchall()

        return [RankRecord(row[0], int(row[1])) for row in rows]


    def InsertRank(self, username : str, problem_id : int, step : int, cookie : str):
        record_time = datetime.now(TZ)
        with MyCursor(need_to_commit = True) as cursor:
            cursor.execute(f"""
                insert into {self.TABLE_NAME}(username, problem_id, step, cookie, record_time)
                values("{username}", {problem_id}, {step}, "{cookie}", "{record_time.strftime(DATETIME_STR_FORMAT)}");
                """
            )


    def UpdateRank(self, cookie : str, problem_id : int, step : int):
        record_time = datetime.now(TZ)
        with MyCursor(need_to_commit = True) as cursor:
            cursor.execute(f"""
                update {self.TABLE_NAME} set
                step = {step}, record_time = "{record_time.strftime(DATETIME_STR_FORMAT)}"
                where cookie = "{cookie}" and problem_id = {problem_id} and step > {step};
                """
            )


    # returns {problem_id: (step, record_time)}, username
    def getUserRecords(self, cookie : str) -> tuple[dict[int, tuple], str]:
        with MyCursor() as cursor:
            cursor.execute(f"""
                select problem_id, step, record_time, username from {self.TABLE_NAME} 
                where cookie = "{cookie}";
                """
            )
            rows = cursor.fetchall()

        name_counting_dict = {}
        record_dict = {}
        for row in rows:
            record_dict[int(row[0])] = int(row[1]), datetime.strptime(str(row[2]), DATETIME_STR_FORMAT)
            name = row[3]
            if name not in name_counting_dict:
                name_counting_dict[name] = 1
            else:
                name_counting_dict[name] += 1

        max_count = None
        username = None
        for name, count in name_counting_dict.items():
            if max_count is None or count > max_count:
                max_count = count
                username = name

        return record_dict, username


    def MergeRecord(self, current_cookie : str, requesting_cookie : str) -> str:
        current_records, _ = self.getUserRecords(current_cookie)
        requesting_cookie_records, requesting_username = self.getUserRecords(requesting_cookie)

        def isBetterRecord(record_x, record_y) -> bool:
            if record_x[0] < record_y[0]:
                return True
            if record_x[0] > record_y[0]:
                return False

            return record_x[1] < record_y[1]

        need_to_update_name_and_cookie = []
        need_to_delete_those_in_requesting = []
        need_to_delete_those_in_current = []
        for problem_id, record in current_records.items():
            if problem_id not in requesting_cookie_records:
                need_to_update_name_and_cookie.append(problem_id)
                continue

            if isBetterRecord(record, requesting_cookie_records[problem_id]):
                need_to_delete_those_in_requesting.append(problem_id)
                need_to_update_name_and_cookie.append(problem_id)
                continue

            need_to_delete_those_in_current.append(problem_id)

        with MyCursor(need_to_commit=True) as cursor:
            def deleteRecords(problem_id : int, cookie : str):
                cursor.execute(f"""
                    delete from {self.TABLE_NAME}
                    where problem_id = {problem_id} and cookie = "{cookie}";
                    """
                )

            for problem_id in need_to_delete_those_in_current:
                deleteRecords(problem_id, current_cookie)
            for problem_id in need_to_delete_those_in_requesting:
                deleteRecords(problem_id, requesting_cookie)

            for problem_id in need_to_update_name_and_cookie:
                cursor.execute(f"""
                    update {self.TABLE_NAME}
                    set cookie = "{requesting_cookie}", username = "{requesting_username}"
                    where problem_id = {problem_id} and cookie = "{current_cookie}";
                    """
                )

        return requesting_username


db = RankDatabase()